package Demo_01;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * 对应数据库tbl_book_info表中的所有操作
 * insert
 * update
 * delete
 * select
 * 如果还有其他表，再创建一个类似的表名Dao
 *
 */
public class BookInfoDao {
    public static void main(String[] args) {
        BookInfoDao bookDao = new BookInfoDao();

        String sql = "insert into tbl_book_info(b_no,b_name,b_price)"+
                " value('1007','特朗普',250)";
        int r = bookDao.executeUpdate(sql);

        if (r>0){
            System.out.println("成功");
        }else {
            System.out.println("失败");
        }

        String sql1 = "select * from tbl_book_info";
        List<Map<String,Object>> books = bookDao.execute(sql1);
        System.out.println(books);



    }

    public List<Map<String,Object>> execute(String sql){
        List<Map<String,Object>> list = new ArrayList();

            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/jdbc? " +
                    "serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false";
            String user = "root";
            String password = "root123";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rst = null;
        try {
            Class.forName(driver);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动类不存在");

        }
        try {
            conn = DriverManager.getConnection(url,user,password);
            stmt = conn.createStatement();
            rst = stmt.executeQuery(sql);


            ResultSetMetaData meta = rst.getMetaData();
            int c = meta.getColumnCount();

            while (rst.next()){
                Map<String,Object> book = new HashMap<>();
                for (int i = 0; i < c; i++) {
                    String key = meta.getColumnLabel(i+1);
                    Object value = rst.getObject(i+1);
                    book.put(key,value);



                }

                list.add(book);

            }



        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int executeUpdate(String sql) {
        String driver = "com.mysql.jdbc.Driver";
        //数据库名在localhost上存在才可以

        String url = "jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false";
        String user = "root";
        String password = "root123";
        Connection conn = null;
        Statement stamt = null;
        try {
            Class.forName(driver);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动类不存在");

        }
        try {
            conn = DriverManager.getConnection(url, user, password);
            sql = "update tbl_book_info set b_no='112' where b_no='123'";
            stamt = conn.createStatement();
            int r = stamt.executeUpdate(sql);
            if (r > 0) {
                System.out.println("执行成功");

            }


        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (stamt != null) {
            try {
                stamt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                stamt = null;
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                conn = null;
            }
        }
        return 0;
    }
    public int executeSelect(){


        return 0;

    }



}


